package com.itheima.dao;

import com.itheima.domain.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

//与数据库交互
public class StudentDao {

    //查询所有
    public List findAll() {
        List list = new ArrayList();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://192.168.26.1:3306/db2", "root", "root");
            //3.获取执行者对象
            stat = con.createStatement();

            //4.执行sql语句，并且接收返回的结果集
            String sql = "SELECT * FROM student2";
            rs = stat.executeQuery(sql);

            //5.处理结果集
            while (rs.next()) {
                Integer sid = rs.getInt("id");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                Student stu = new Student();
                //封装Student对象
                stu.setSid(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setBirthday(birthday);
                list.add(stu);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        //将对象返回
        return list;
    }

    //插入操作
    public void insert(Student stu) {

        Connection con = null;
        Statement stat = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://192.168.26.1:3306/db2", "root", "root");
            //3.获取执行者对象
            PreparedStatement preparedStatement = con.prepareStatement("insert  into  student2 values(null,?,?,?)");

            preparedStatement.setString(1, stu.getName());
            preparedStatement.setInt(2, stu.getAge());

            Date birthday1 = stu.getBirthday();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String format = simpleDateFormat.format(birthday1);
            //设置生日
            preparedStatement.setString(3, format);

            preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public void delete(String id) {
        Connection con = null;
        Statement stat = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://192.168.26.1:3306/db2", "root", "root");
            //3.获取执行者对象
            PreparedStatement preparedStatement = con.prepareStatement("delete from  student2 where id=?");

            preparedStatement.setString(1, id);
            preparedStatement.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
